<?php
namespace app\controller;

use app\BaseController;
use think\facade\Request;
use think\facade\Db;
use think\Exception;
use think\facade\Filesystem;
use think\facade\Log;

class AssetOverview extends BaseController
{
    //获取资产的总数，总价，在用数量，闲置数量，报废数量，维修数量
    public function getAssetOverall()
    {
        $userId = $this->request->auth['data']->user->user_id;


        // 根据用户ID获取公司ID
        $companyId = Db::name('user')->where('user_id', $userId)->value('user_company_id');

        try {
            $data = Db::table('assets')
                ->field('count(*) as total,
             sum(assets_PurchasePrice) as total_price,
             count(if(assets_status="在用" OR assets_status="在用/公共", "在用", null)) as in_use,
             count(if(assets_status="闲置", "闲置", null)) as idle,
             count(if(assets_status="报废", "报废", null)) as scrap,
             count(if(assets_status="维修中", "维修中", null)) as repair')
                ->where('assets_company_id', $companyId)
                ->where('assets_InputStatus','已审核')
                ->find();

            // 获取当前月份
            $currentMonth = date('Y-m');

            // 获取各月度汇总和增长率
            $instockSummary = $this->getMonthlySummary('instock', 'instock_creationtime', 'instock_quantity', $companyId, $currentMonth);
            $useBillSummary = $this->getMonthlySummary('AssetUseBill', 'AssetUseBill_creationtime', 'AssetUseBill_quantity', $companyId, $currentMonth);
            $returnsBillSummary = $this->getMonthlySummary('AssetReturnsBill', 'AssetReturnsBill_creationtime', 'AssetReturnsBill_quantity', $companyId, $currentMonth);



            $data['instock_currentMonthAdd']=$instockSummary;//新入库
            $data['AssetUseBill_currentMonthAdd']=$useBillSummary+$this->getAssetStatusCount($companyId,'在用',$currentMonth);//新在用
            $data['AssetReturnsBill_currentMonthAdd']=$returnsBillSummary+$this->getAssetStatusCount($companyId,'闲置',$currentMonth);//新退库
            $data['Asset_AddPrice']=$this->getAssetCurrentMonthPrice($companyId,$currentMonth);//新增资产额度






            return json(['code' => 200, 'msg' => '获取成功', 'data' => $data]);
        }
        catch (Exception $e) {
            return json(['code' => 500, 'msg' => '获取失败', 'data' => $e->getMessage()]);
        }
    }


    /**
     * 获取指定公司在一个特定月份的汇总数据
     * 该方法用于查询指定表格中，在特定月份创建且状态为已审核的记录的总量。
     *
     * @param string $tableName 表格名称，用于查询数据的表格。
     * @param string $creationTimeField 创建时间字段名，指定记录的创建时间存储在表格的哪个字段。
     * @param string $quantityField 数量字段名，指定需要汇总的数量字段。
     * @param int $companyId 公司ID，用于限定查询的数据属于哪个公司。
     * @param string $currentMonth 月份字符串，格式为"YYYY-MM"，指定需要查询的月份。
     * @return int 返回指定月份的汇总数量，如果没有数据则返回0。
     */
    protected function getMonthlySummary($tableName, $creationTimeField, $quantityField, $companyId, $currentMonth)
    {

        $currentMonthData = Db::table($tableName)
            ->field('SUM(' . $quantityField . ') as total_quantity')
            ->whereRaw('DATE_FORMAT(' . $creationTimeField . ', "%Y-%m") = ?', [$currentMonth])
            ->where($tableName . '_company_id', $companyId)
            ->where($tableName . '_state', '已审核')
            ->find();



        return  $currentMonthData['total_quantity'] ?? 0;


    }
    //获取资产表中,根据state获取当月状态的数量
    protected function getAssetStatusCount($companyId,$state,$currentMonth)
    {
        $data = Db::table('assets')
            ->field('assets_status, count(*) as count')
            ->whereRaw('DATE_FORMAT(assets_creationtime, "%Y-%m") = ?', [$currentMonth])
            ->where('assets_company_id', $companyId)
            ->where('assets_InputStatus','已审核')
            ->where('assets_status',$state)
            ->find();
        return $data['count'] ?? 0;
    }
    //获取资产表中,本月新增的资产额度
  protected function getAssetCurrentMonthPrice($companyId,$currentMonth)
  {
    $data = Db::table('assets')
        ->field('sum(assets_PurchasePrice) as total_price')
        ->whereRaw('DATE_FORMAT(assets_creationtime, "%Y-%m") = ?', [$currentMonth])
        ->where('assets_company_id', $companyId)
        ->where('assets_InputStatus','已审核')
        ->find();
    return $data['PurchasePrice'] ?? 0;
   }


   //统计各类型资产数量
    public function getAssetTypeCount()
    {
        $userId = $this->request->auth['data']->user->user_id;


        // 根据用户ID获取公司ID
        $companyId = Db::name('user')->where('user_id', $userId)->value('user_company_id');

        try {
            $data = Db::table('vw_assets')
                ->field('assetTypes_name as name, count(*) as value')
                ->where('assets_company_id', $companyId)
                ->where('assets_InputStatus','已审核')
                ->group('name')
                ->select();

            return json(['code' => 200, 'msg' => '获取成功', 'data' => $data]);
        }
        catch (Exception $e) {
            return json(['code' => 500, 'msg' => '获取失败', 'data' => $e->getMessage()]);
            }
        }


     //统计各部门各类别资产数量
    public function getAssetTypeCountByDepartment()
    {
        $userId = $this->request->auth['data']->user->user_id;


        // 根据用户ID获取公司ID
        $companyId = Db::name('user')->where('user_id', $userId)->value('user_company_id');

        try {
            $data = Db::table('vw_assets')
                ->alias('a')
                ->join('department d', 'a.assets_department_id = d.department_id')
                ->join('AssetTypes at', 'a.assets_AssetTypes_id = at.AssetTypes_id')
                ->field('d.department_name, at.AssetTypes_name, COUNT(*) as asset_count')
                ->group('d.department_name, at.AssetTypes_name')
                ->where('a.assets_company_id', $companyId)
                ->where('a.assets_InputStatus','已审核')
                ->select()->toArray();

            $result = [];

            foreach ($data as $row) {
                $result[$row['department_name']]['name'] = $row['department_name'];
                $result[$row['department_name']]['data'][] = [
                    'type' => $row['AssetTypes_name'],
                    'value' => $row['asset_count']
                ];
            }
            $result = array_values($result);



            return json(['code' => '200', 'msg' => '获取成功','data' => $result]);

        }
        catch (Exception $e) {
            return json(['code' => 500, 'msg' => '获取失败', 'data' => $e->getMessage()]);
        }



    }






}